package cn.edu.zjgsu.ito.service;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import cn.edu.zjgsu.ito.model.TeacherModel;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminExportScoreService {
	/*
	 * 张虎涛
	 * 2016/06/01
	 */
	public List<Record> CourseGrade(String uuid) {
		try {
			
			List<Record> record=Db.find(TeacherModel.dao.selectTgSql,uuid);
			return record;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}
	
	public Record TTT(String uuid){
		 try {
				Record record=Db.findFirst("select * from teacher_timetable where uuid=?",uuid);

				return record;
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		 
	 }
	
	/**
	 * 创建excel
	 * 导出学生成绩
	 */

	 public void exportExcel(

			 List<Record> dataset, OutputStream out,Record record) {

		 HSSFWorkbook workbook = new HSSFWorkbook();      
         HSSFSheet sheet = workbook.createSheet("new   sheet");      
         HSSFCellStyle style = workbook.createCellStyle(); // 样式对象 
         HSSFCellStyle style1 = workbook.createCellStyle(); // 样式对象
         //字体  加粗
         HSSFFont font1 = workbook.createFont();

	  	 font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	  	 font1.setFontHeightInPoints((short) 12);//设置字体大小
	  	 font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	  	 
	  	//字体  加粗
         HSSFFont font = workbook.createFont();

	  	 font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	  	 font.setFontHeightInPoints((short) 12);//设置字体大小
         
	  	    // 设置这些样式

	      style.setFillForegroundColor(HSSFColor.WHITE.index);

	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

	      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

	      style.setBorderRight(HSSFCellStyle.BORDER_THIN);

	      style.setBorderTop(HSSFCellStyle.BORDER_THIN);

	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  
	      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
       
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
	      		             
	      style.setFont(font1);
	      
	      
	      style1.setFillForegroundColor(HSSFColor.WHITE.index);

	      style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	      style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

	      style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

	      style1.setBorderRight(HSSFCellStyle.BORDER_THIN);

	      style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

	      style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  
	      style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
       
	      style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
	      
	      style1.setFont(font);
       
       
	       int width=5000;
	       int height=500;
	       int height1=350;
	       
	       HSSFRow row = sheet.createRow((short) 0);
	       HSSFRow row1 = sheet.createRow((short) 1);
	       HSSFRow row2 = sheet.createRow((short) 2);
	       HSSFRow row3 = sheet.createRow((short) 3);
	       sheet.addMergedRegion(new Region(0, (short) 1, 0, (short) 1));// 第一个参数：开始行  第二个参数：开始列  第三个参数：结束行 第四个参数：结束列    
	       sheet.addMergedRegion(new Region(0, (short) 2, 0, (short) 3));
	       sheet.addMergedRegion(new Region(0, (short) 9, 0, (short) 10));            
	       sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 4));
	         
         
         
         
         
         sheet.createRow(0).setHeight((short) height);//第一行
         sheet.createRow(1).setHeight((short) height1);//第二行
         sheet.createRow(2).setHeight((short) height1);//第三行
         sheet.createRow(3).setHeight((short) height1);//第四行
   
         //列宽
         sheet.setColumnWidth(0, 1000);
         for(int i=1;i<11;i++){
         	sheet.setColumnWidth(i, width);
         }
         
         Iterator<Record> it = dataset.iterator();

         //第一行
         HSSFCell ce = row.createCell((short) 1);          
         ce.setCellValue("课程名");      
         ce.setCellStyle(style);
         HSSFCell ce0 = row.createCell((short) 2);          
         ce0.setCellValue(record.getStr("courseName"));      
         ce0.setCellStyle(style);
         
         HSSFCell ce1 = row.createCell((short) 4);           
         ce1.setCellValue("授课教师");   
         ce1.setCellStyle(style);
         HSSFCell ce001 = row.createCell((short) 5);           
         ce001.setCellValue(record.getStr("tNo"));   
         ce001.setCellStyle(style); 
                     
         HSSFCell ce2 = row.createCell((short) 6);           
         ce2.setCellValue("起始周");    
         ce2.setCellStyle(style);
         HSSFCell ce002 = row.createCell((short) 7);           
         ce002.setCellValue(record.getStr("startWeek")+"-"+record.getStr("endWeek"));    
         ce002.setCellStyle(style);
                     
         HSSFCell ce3 = row.createCell((short) 8);           
         ce3.setCellValue("上课时间");    
         ce3.setCellStyle(style);
         HSSFCell ce03 = row.createCell((short) 9);           
         ce03.setCellValue(record.getStr("startLesson")+"-"+record.getStr("endLesson"));    
         ce03.setCellStyle(style);
         
         HSSFCell ce003 = row.createCell((short) 10);
         ce003.setCellStyle(style);
         
//         for(int i=1;i<11;i++){
////         	if(i!=1&&i!=4&&i!=6&&i!=8){
//         		HSSFCell ce00 = row1.createCell((short) i);
//         		ce00.setCellStyle(style);
////         	}
//         }
         
         //第二行
         
         HSSFCell ce4 = row1.createCell((short) 1);
         ce4.setCellValue("专业");
         ce4.setCellStyle(style);
         HSSFCell ce5 = row1.createCell((short) 5);
         ce5.setCellValue("入学年份");
         ce5.setCellStyle(style);

         
         HSSFCell ce6 = row1.createCell((short) 7);
         ce6.setCellValue("入学季度");
         ce6.setCellStyle(style);
         
         for(int i=1;i<11;i++){
         	if(i!=1&&i!=5&&i!=7){
         		HSSFCell ce01 = row1.createCell((short) i);
         		ce01.setCellStyle(style);
         	}
         }
         
         //第三行
         double min=100,max=0;
         int COUNT=0;
         int count=0;
         for(int i=0;i<dataset.size();i++){
             String grade=dataset.get(i).getStr("grade");
             double score=0;
             if(grade.equals("优秀")||grade.equals("良好")||grade.equals("中等")||grade.equals("及格")||grade.equals("不及格")){
                 if(grade.equals("优秀")){
                     score=95;
                 }
                 if(grade.equals("良好")){
                     score=85;
                 }
                 if(grade.equals("中等")){
                     score=75;
                 }
                 if(grade.equals("及格")){
                     score=65;
                 }
                 if(grade.equals("不及格")){
                     score=55;
                 }
             }else{
                 score=Double.parseDouble(dataset.get(i).getStr("grade"));
             }
             System.out.println("分数"+score);
        	 if(score>=max)
        		 max=score;
                if(score<=min)
                    min=score;
                if(score==0)
                    count++;
                COUNT+=score;
        	 
         }
         HSSFCell ce7 = row2.createCell((short) 1);
     	 ce7.setCellValue("应考人数");
         ce7.setCellStyle(style);         
         HSSFCell ce07 = row2.createCell((short) 2);
         ce07.setCellValue(dataset.size());
         ce07.setCellStyle(style1);
         
         HSSFCell ce8 = row2.createCell((short) 3);
     	 ce8.setCellValue("实考人数");
         ce8.setCellStyle(style);
         HSSFCell ce08 = row2.createCell((short) 4);
         ce08.setCellValue((dataset.size()-count));
         ce08.setCellStyle(style);
         
         HSSFCell ce9 = row2.createCell((short) 5);
     	 ce9.setCellValue("最低分");
         ce9.setCellStyle(style);
         HSSFCell ce09 = row2.createCell((short) 6);
         ce09.setCellValue(min);
         ce09.setCellStyle(style);
         
         HSSFCell ce10 = row2.createCell((short) 7);
     	 ce10.setCellValue("最高分");
         ce10.setCellStyle(style);
         HSSFCell ce010 = row2.createCell((short) 8);
         ce010.setCellValue(max);
         ce010.setCellStyle(style);
         
         
         HSSFCell ce11 = row2.createCell((short) 9);
     	 ce11.setCellValue("平均分");
         ce11.setCellStyle(style);
         HSSFCell ce011 = row2.createCell((short) 10);
         ce011.setCellValue((COUNT/dataset.size()));
         ce011.setCellStyle(style);
         
//         for(int i=1;i<11;i++){
////         	if(i!=1&&i!=3&&i!=5&&i!=7&&i!=9){
//         		HSSFCell ce02 = row1.createCell((short) i);
//         		ce02.setCellStyle(style);
////         	}
//         }
         
         //第四行
         String[] Headers = { "序号", "学号", "护照号", "姓", "名", "平时成绩", "实验成绩", "期末成绩","其他成绩","总成绩"};
         for (short i = 0; i <Headers.length; i++) {

	         HSSFCell cell = row3.createCell(i+1);

	         cell.setCellStyle(style);

	         HSSFRichTextString text = new HSSFRichTextString(Headers[i]);

	         cell.setCellValue(text);

	      }
	      
         try{
        	 int index = 3,order=1;
   	      
   	      while (it.hasNext()) {
   	    	  index++;

   	          row = sheet.createRow(index);

   	          Record t = (Record) it.next();
   	          
   	          String[] Context = { String.valueOf(order++),t.getStr("sNo"), t.getStr("passportNo"), t.getStr("lastName"), t.getStr("firstName"),t.getStr("dailyGrade"), t.getStr("experimentalGrade"),t.getStr("otherGrade"), t.getStr("finalGrade"),t.getStr("grade")};
   	          for (short i = 0; i < Headers.length; i++) {

   	 	         HSSFCell cell = row.createCell(i+1);

   	 	         cell.setCellStyle(style1);

   	 	         HSSFRichTextString text = new HSSFRichTextString(Context[i]);

   	 	         cell.setCellValue(text);

   	 	      }
 	    	  
   	      }
        	 
         }catch(Exception e){
        	 e.printStackTrace();
         }
         
	      
    

	      try {

	         workbook.write(out);

	      } catch (IOException e) {

	         // TODO Auto-generated catch block

	         e.printStackTrace();

	      }

	 

	   }

}
